package cn.jgls.bi.dataprocess;

import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.Date;
import java.util.Iterator;
//import java.util.Calendar;
//import java.util.Calendar;
//import java.text.SimpleDateFormat;
/**
 *
 * @author fish
 */
public class DPJDSale extends JdbcDaoSupport implements DPDSale { 
    
    class getTsnSortIdRM implements RowMapper {

        @Override
        public Object mapRow(ResultSet rs, int index) throws SQLException {
            return rs.getInt(1);
        }
    }
    @Override
    public List getTsnSortId() {
        JdbcTemplate jt = getJdbcTemplate();
        return jt.query("select id from t_bi_tsnsort where bj=1 order by id",
                new RowMapperResultReader(new getTsnSortIdRM()));
    }
    
    //取计划数据开始
    class setSaleJHRM implements RowMapper {
            public Object mapRow(ResultSet rs, int index)
                    throws SQLException {
                SaleJH Sjh = new SaleJH();
                Sjh.setSaleJH_value(new Float(rs.getFloat("jhje")));
                return Sjh;
            }
        }
    //取计划数据结束
    
    class setSaleSumRM implements RowMapper {
        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            SaleSumMx SaleSum = new SaleSumMx();
            SaleSum.setSaleValue(new Float(rs.getFloat("xsje")));
            SaleSum.setJinhuo_value(new Float(rs.getFloat("jjje")));
            SaleSum.setArea(new Float(rs.getFloat("area")));
            SaleSum.setPeople(new Integer(rs.getInt("people")));
            return SaleSum;
        }
    }
    class setSaleWeekHzRM implements RowMapper {
        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            SaleSumWeekHz SSWH = new SaleSumWeekHz();
            SSWH.setSale_week_addup(new Float(rs.getFloat("sale_week_addup")));
            SSWH.setGrsprt_week_addup(new Float(rs.getFloat("grsprt_week_addup")));
            SSWH.setSale_week_amt(new Integer(rs.getInt("Sale_week_amt")));
            SSWH.setGrsprt_week_amt(new Integer(rs.getInt("Grsprt_week_amt")));
            return SSWH;
        }
    }
    class setSaleYearHzRM implements RowMapper {
        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            SaleSumYearHz SSYH = new SaleSumYearHz();
            SSYH.setSale_year_addup(new Float(rs.getFloat("sale_year_addup")));
            SSYH.setGrsprt_year_addup(new Float(rs.getFloat("grsprt_year_addup")));
            SSYH.setSale_year_amt(new Integer(rs.getInt("Sale_year_amt")));
            SSYH.setGrsprt_year_amt(new Integer(rs.getInt("Grsprt_year_amt")));
            return SSYH;
        }
    }
    
    public void SaleAmt(Date adTsnDate, int aiShopId, String asShopBm,int aiDateNo, String asYear, int aiYybh, int aiMan_id,String sTname){
            int iRc;
            JdbcTemplate jt = getJdbcTemplate();
            int TsnSort=0;
            String sql="",sql1="",sqljh="";
            Float SaleWeekAddup=0f,GrsprtWeekAddup=0f;
            Float SaleYearAddup=0f,GrsprtYearAddup=0f,jhje=0f;
            int SaleWeekAmt=0,GrsprtWeekAmt=0;
            int SaleYearAmt=0,GrsprtYearAmt=0;
            
            
//            Calendar c = Calendar.getInstance();
//            c.setTime(adTsnDate);
//            c.add(Calendar.DATE,-1);
//            java.util.Date Yesterday=c.getTime();
        //该过程每天运行一次，提前取出所有的数据，供后面获取，所以目前定为2店，如2店闭店，请换为其他门店
        if (aiShopId == 2) {
            //清空临时表
            sql="delete from t_bi_sale_mx_tmp";  //此处没有加tsn_sort标记，注意！！
            iRc = jt.update(sql);
            //全部商品
            sql="insert into t_bi_sale_mx_tmp (tsn_id,xsje,jjje,tsn_sort) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,1 as tsn_sort    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and a.bm=b.bm and substr(lb,1,4)!='0829'  "+
                          " group by id" ;
            Object[] params = new Object[]{adTsnDate};
            int[] types = new int[]{Types.DATE};
            iRc = jt.update(sql, params, types);

            ////包装商品
            sql="insert into t_bi_sale_mx_tmp (tsn_id,xsje,jjje,tsn_sort) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,2 as tsn_sort    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and (substr(lb,1,2) != '08' and substr(lb,1,2) != '19' and substr(lb,1,1) != '5' and substr(lb,1,1) != '6' ) " +
                          "   and a.bm=b.bm and substr(lb,1,4)!='0829'  "+
                          " group by id" ;
            iRc = jt.update(sql, params, types);
            ////大肉
            sql="insert into t_bi_sale_mx_tmp (tsn_id,xsje,jjje,tsn_sort) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,3 as tsn_sort    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and substr(lb,1,3)='089' " +
                          "   and a.bm=b.bm "+
                          " group by id" ;
            iRc = jt.update(sql, params, types);
            //蛋
            sql="insert into t_bi_sale_mx_tmp (tsn_id,xsje,jjje,tsn_sort) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,4 as tsn_sort    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and lb in (select lb from sy_sp_mx where pl='鸡蛋')" +
                          "   and a.bm=b.bm "+
                          " group by id" ;
            iRc = jt.update(sql, params, types);
            //菜
            sql="insert into t_bi_sale_mx_tmp (tsn_id,xsje,jjje,tsn_sort) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,5 as tsn_sort    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and lb in (select lb from sy_sp_mx where pl='蔬菜')" +
                          "   and a.bm=b.bm "+
                          " group by id" ;
            iRc = jt.update(sql, params, types);
            //面食
            sql="insert into t_bi_sale_mx_tmp (tsn_id,xsje,jjje,tsn_sort) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,6 as tsn_sort    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and lb in (select lb from sy_sp_mx where pl='面食')" +
                          "   and a.bm=b.bm "+
                          " group by id" ;
            iRc = jt.update(sql, params, types);
              //主食厨房
            sql="insert into t_bi_sale_mx_tmp (tsn_id,xsje,jjje,tsn_sort) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,7 as tsn_sort    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and lb in (select lb from sy_zscf_new09)" +
                          "   and a.bm=b.bm "+
                          " group by id" ;
            iRc = jt.update(sql, params, types);
            //水果
            sql="insert into t_bi_sale_mx_tmp (tsn_id,xsje,jjje,tsn_sort) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,8 as tsn_sort    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and lb in (select lb from sy_sp_mx where pl='水果')" +
                          "   and a.bm=b.bm "+
                          " group by id" ;
            iRc = jt.update(sql, params, types);
            //牛羊肉
            sql="insert into t_bi_sale_mx_tmp (tsn_id,xsje,jjje,tsn_sort) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,9 as tsn_sort    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and lb in ('087631','087632','087644')" +
                          "   and a.bm=b.bm "+
                          " group by id" ;
            iRc = jt.update(sql, params, types);
            //酱货
            sql="insert into t_bi_sale_mx_tmp (tsn_id,xsje,jjje,tsn_sort) "+
                  "select id as tsn_id,nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,10 as tsn_sort    "+
                             "  from "+sTname+" a,sy_archi_dept b   "+
                          " where a.saledate=?"+
                          "   and trim(a.time) is not null and a.trnno<>9999  "+
                          "   and lb in (SELECT lb FROM sy_SXK WHERE PQBM='81008' OR PQBM='81009') " +
                          "   and a.bm=b.bm "+
                          " group by id" ;
            iRc = jt.update(sql, params, types);

        }
            
            
            //在插入t_bi_sale_mx前，先删除此店、此日期的数据
            sql="delete from t_bi_sale_mx where tsn_date=? and tsn_id=?";  //此处没有加tsn_sort标记，注意！！
            Object[] params = new Object[]{adTsnDate,aiShopId};
            int[] types = new int[]{Types.DATE,Types.INTEGER};
            iRc = jt.update(sql, params, types);
            
            Iterator tskIter = getTsnSortId().iterator();  //得到要计算的是那个项目，全部、包装等等！
            while (tskIter.hasNext()) {
                //System.out.println(aiShopId+"****"+tskIter.next().toString());
                
                switch((Integer)tskIter.next()){
                    case 1:
                        sql = "select nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,b.area,b.people " + 
                              " from "+sTname+" a,sy_archi_dept b" + 
                              " where a.bm=b.bm and substr(lb,1,4)!='0829' and a.saledate = ? and b.id= ? and a.trnno<>9999" + 
                              " group by b.area,b.people";
                        TsnSort=1;
                        break;
                    case 2: //包装商品
                        sql = "select nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,b.area,b.people " + 
                              " from "+sTname+" a,sy_archi_dept b" + 
                              " where a.bm=b.bm and substr(lb,1,4)!='0829' and (substr(lb,1,3) not in ('089','086','087','191') and substr(lb,1,1) != '5' and substr(lb,1,1) != '6') and a.saledate = ? and b.id= ? and a.trnno<>9999" +
                              " group by b.area,b.people";
                        TsnSort=2;
                        break;
                    case 3: //大肉
                        sql = "select nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,b.area,b.people " + 
                              " from "+sTname+" a,sy_archi_dept b" + 
                              " where a.bm=b.bm and substr(lb,1,3)='089' and a.saledate = ? and b.id= ? and a.trnno<>9999" + 
                              " group by b.area,b.people";
                        TsnSort=3;
                        break;
                    case 4: //蛋
                        sql = "select nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,b.area,b.people " + 
                              " from "+sTname+" a,sy_archi_dept b" + 
                              " where a.bm=b.bm and lb IN ( select lb from sy_sp_mx where pl='鸡蛋') and a.saledate = ? and b.id= ? and a.trnno<>9999" +
                              " group by b.area,b.people";
                        TsnSort=4;
                        break;    
                    case 5: //菜
                        sql = "select nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,b.area,b.people " + 
                              " from "+sTname+" a,sy_archi_dept b" + 
                              " where a.bm=b.bm and lb IN ( select lb from sy_sp_mx where pl='蔬菜') and a.saledate = ? and b.id= ? and a.trnno<>9999" +
                              " group by b.area,b.people";
                        TsnSort=5;
                        break;
                    case 6: //面食
                        sql = "select nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,b.area,b.people " + 
                              " from "+sTname+" a,sy_archi_dept b" + 
                              " where a.bm=b.bm and lb IN ( select lb from sy_sp_mx where pl='面食') and a.saledate = ? and b.id= ? and a.trnno<>9999" +
                              " group by b.area,b.people";
                        TsnSort=6;
                        break;
                    case 7: //主食厨房
                        sql = "select nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,b.area,b.people " + 
                              " from "+sTname+" a,sy_archi_dept b" + 
                              " where a.bm=b.bm and lb IN (select lb from sy_zscf_new09 ) and a.saledate = ? and b.id= ? and a.trnno<>9999" +
                              " group by b.area,b.people";
                        TsnSort=7;
                        break;
                    case 8: //水果
                         sql = "select nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,b.area,b.people " + 
                              " from "+sTname+" a,sy_archi_dept b" + 
                              " where a.bm=b.bm and lb IN ( select lb from sy_sp_mx where pl='水果') and a.saledate = ? and b.id= ? and a.trnno<>9999" +
                              " group by b.area,b.people";
                        TsnSort=8;
                        break;
                    case 9:  //牛羊肉
                        sql = "select nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,b.area,b.people " + 
                              " from "+sTname+" a,sy_archi_dept b" + 
                              " where a.bm=b.bm and lb in ('087631','087632','087644') and a.saledate = ? and b.id= ? and a.trnno<>9999" + 
                              " group by b.area,b.people";
                        TsnSort=9;
                        break;
                    case 10:  //酱货
                        sql = "select nvl(sum(a.je),0) as xsje,nvl(sum(a.jjje),0) as jjje,b.area,b.people " + 
                              " from "+sTname+" a,sy_archi_dept b" + 
                              " where a.bm=b.bm and lb in (SELECT lb FROM sy_SXK WHERE PQBM='81008' OR PQBM='81009') and a.saledate = ? and b.id= ? and a.trnno<>9999" + 
                              " group by b.area,b.people";
                        TsnSort=10;
                        break;
                }
                //新方法，从tmp表中取数，直接覆盖上面的sql语句内容
                sql = "select a.xsje,a.jjje,b.area,b.people " +
                      " from t_bi_sale_mx_tmp a,sy_archi_dept b" +
                      " where a.tsn_id=b.id and tsn_sort = "+TsnSort+"   and b.id= ?  " ;
                
                Float MaoLiE=0f;
                SaleSumMx SSM=new SaleSumMx();
                params = new Object[]{aiShopId};
                types = new int[]{Types.INTEGER};
                List query = jt.query(sql, params, new RowMapperResultReader(new setSaleSumRM()));
                Iterator tskIter1 = query.iterator();
                
                //将变量清零
                SaleWeekAddup=0f;
                GrsprtWeekAddup=0f;
                SaleWeekAmt=0;
                GrsprtWeekAmt=0;
                SaleYearAddup=0f;
                GrsprtYearAddup=0f;
                SaleYearAmt=0;
                GrsprtYearAmt=0;
                
                while(tskIter1.hasNext()){   //取得sql并运行！
                    SSM=(SaleSumMx)tskIter1.next();
                    if (SSM.getSaleValue()==0) {
                        MaoLiE=0f;}
                    else{ 
                        MaoLiE=SSM.getSaleValue()-SSM.getJinhuo_value();}
                    
                    
                    //取计划数开始
                    if (TsnSort==1) {
                        sqljh="select (a.zb*b.bl)*100*b.rjbl/366 as jhje from sy_scale_yyb_zb a,sy_scale_md_rj b " +
                              " where b.lsdid=? and a.yybh=b.yybh and b.saledate=?";
                        SaleJH sjh=new SaleJH();
                        params = new Object[]{aiShopId,adTsnDate};
                        types = new int[]{Types.INTEGER,Types.DATE};
                        query = jt.query(sqljh,params,types,new RowMapperResultReader(new setSaleJHRM()));
                        Iterator tskIter4 = query.iterator();
                        while(tskIter4.hasNext()){
                            sjh=(SaleJH)tskIter4.next();
                            jhje=sjh.getSaleJH_value();
                        }
                    }
                    else {
                        jhje=0f;
                    }
                    //取计划数结束
                    
                    //周数据
                    sql1 = "select sale_week_addup,sale_week_amt," +
                            "      grsprt_week_addup,grsprt_week_amt " +
                            " from t_bi_sale_mx " +
                            "where tsn_date=(? - 7) " +
                            "  and tsn_date_no=? and tsn_id=? and tsn_sort=? and tsn_year=?";
                    SaleSumWeekHz SSWH=new SaleSumWeekHz();
                    params = new Object[]{adTsnDate, aiDateNo,     aiShopId,     TsnSort,      Integer.parseInt(asYear)};
                    types = new int[]{    Types.DATE, Types.INTEGER,Types.INTEGER,Types.INTEGER,Types.INTEGER};
                    query = jt.query(sql1, params, new RowMapperResultReader(new setSaleWeekHzRM()));
                    Iterator tskIter2 = query.iterator();
                    while(tskIter2.hasNext()){
                         SSWH=(SaleSumWeekHz)tskIter2.next();
                         SaleWeekAddup=SSWH.getSale_week_addup();             //周销售汇总
                         GrsprtWeekAddup=SSWH.getGrsprt_week_addup();         //周毛利汇总
                         SaleWeekAmt=SSWH.getSale_week_amt();                 //周销售次数汇总
                         GrsprtWeekAmt=SSWH.getGrsprt_week_amt();             //周毛利次数汇总
                     }
                    SaleWeekAddup=SaleWeekAddup+SSM.getSaleValue();
                    GrsprtWeekAddup=GrsprtWeekAddup+MaoLiE;
                    SaleWeekAmt++;
                    GrsprtWeekAmt++;
                    //周数据结束
                    //年数据
                    sql1 = "select sale_year_addup,sale_year_amt," +
			   "      grsprt_year_addup,grsprt_year_amt " +	 
			   " from t_bi_sale_mx " +
			   "where tsn_date = (? - 1) " +
                           "and tsn_id=? and tsn_sort=? and tsn_year=?";
                    
                    SaleSumYearHz SSYH=new SaleSumYearHz();
                    params = new Object[]{adTsnDate, aiShopId,      TsnSort,      Integer.parseInt(asYear)};
                    types = new int[]{    Types.DATE, Types.INTEGER, Types.INTEGER,Types.INTEGER};
                    query = jt.query(sql1, params, new RowMapperResultReader(new setSaleYearHzRM()));
                    Iterator tskIter3 = query.iterator();
                    while(tskIter3.hasNext()){
                        SSYH=(SaleSumYearHz)tskIter3.next();
                        SaleYearAddup=SSYH.getSale_year_addup();
                        GrsprtYearAddup=SSYH.getGrsprt_year_addup();
                        SaleYearAmt=SSYH.getSale_year_amt();
                        GrsprtYearAmt=SSYH.getGrsprt_year_amt();
                    }
                        SaleYearAddup=SaleYearAddup+SSM.getSaleValue();
                        GrsprtYearAddup=GrsprtYearAddup+MaoLiE;
                        SaleYearAmt++;
                        GrsprtYearAmt++;
                        
                    //年数据结束
                    params = new Object[]{adTsnDate,     aiShopId,       SSM.getSaleValue(),MaoLiE,     
                        Integer.parseInt(asYear),        aiDateNo,       aiYybh,            aiMan_id,     
                                          TsnSort,       SSM.getArea(),  SSM.getPeople(),   SaleWeekAddup,
                                          SaleWeekAmt,   GrsprtWeekAddup,GrsprtWeekAmt,     SaleYearAddup,
                                          SaleYearAmt,   GrsprtYearAddup,GrsprtYearAmt,     jhje
                                          };
                    types = new int[]{    Types.DATE,    Types.INTEGER,  Types.FLOAT,       Types.FLOAT, 
                                          Types.INTEGER, Types.INTEGER,  Types.INTEGER,     Types.INTEGER,
                                          Types.INTEGER, Types.FLOAT,    Types.INTEGER,     Types.FLOAT,
                                          Types.INTEGER, Types.FLOAT,    Types.INTEGER,     Types.FLOAT,
                                          Types.INTEGER, Types.FLOAT,    Types.INTEGER,     Types.FLOAT
                                      };
                    sql = "insert into t_bi_sale_mx " +
                            " (tsn_date," +
                            "  tsn_id," +
                            "  sale_value," +
                            "  sale_grsprt," +
                            "  tsn_year," +
                            "  tsn_date_no," +
                            "  yybh," +
                            "  man_id," +
                            "  tsn_sort," +
                            "  area," +
                            "  people, " + 
                            "  sale_week_addup, " + 
                            "  sale_week_amt, " + 
                            "  grsprt_week_addup, " + 
                            "  grsprt_week_amt," +
                            "  sale_year_addup, " +
                            "  sale_year_amt," +
                            "  grsprt_year_addup," +
                            "  grsprt_year_amt," +
                            "  sale_plan) " + 
                            " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    iRc = jt.update(sql, params, types);
                    System.out.println("now process sale data,shopid:"+aiShopId+" Tsnsort:"+TsnSort+" value:"+SSM.getSaleValue());
                    //System.out.println(aiShopId+"****"+SSM.getSaleValue()+"****"+TsnSort);
                }
             //完成
           }

            
            
    }
}
